import pandas as pd
import numpy as np

# 读取Excel文件
file_path = '钢网编号.xlsx'

# 处理第一个sheet（钢网）
df1 = pd.read_excel(file_path, sheet_name=0)
print("第一个sheet数据:")

# 处理第二个sheet（刮刀）
df2 = pd.read_excel(file_path, sheet_name=1)
print("第二个sheet数据:")

# 存储所有要插入的数据
all_data = []

# 处理第一个sheet - 钢网数据
for _, row in df1.iterrows():
    # 跳过空行
    if pd.isna(row).all():
        continue

    # 提取产品料号、B/T面和钢网编号
    try:
        # 确保从正确的列读取数据（B列为产品料号，C列为B/T面，D列为钢网编号）
        product_id = str(row[1]) if pd.notna(row[1]) else None  # B列 - 产品料号
        surface = str(row[2]) if pd.notna(row[2]) else None      # C列 - B/T面
        fix_sn = str(row[3]) if pd.notna(row[3]) else None       # D列 - 钢网编号

        if product_id and fix_sn:
            print(f"产品料号: {product_id}, B/T面: {surface}, 钢网编号: {fix_sn}")
            all_data.append({
                'product_id': product_id,
                'product_name': None,  # 设置为NULL
                'fix_type': '钢网',
                'fix_sn': fix_sn,  # 钢网编号
                'fix_status': '在库',
                'fix_surface': surface  # B/T面
            })
    except Exception as e:
        print(f"处理钢网数据行时出错: {e}")

# 处理第二个sheet - 刮刀数据
for _, row in df2.iterrows():
    # 跳过空行
    if pd.isna(row).all():
        continue

    # 提取产品料号和治具编号
    try:
        product_id = str(row[1]) if pd.notna(row[1]) else None  # B列 - 产品料号
        fix_sn = str(row[2]) if pd.notna(row[2]) else None      # C列 - 治具编号

        if product_id and fix_sn:
            print(f"产品料号: {product_id}, 治具编号: {fix_sn}")
            all_data.append({
                'product_id': product_id,
                'product_name': None,  # 设置为NULL
                'fix_type': '刮刀',
                'fix_sn': fix_sn,
                'fix_status': '在库',
                'fix_surface': None  # 第二个sheet没有B/T面
            })
    except Exception as e:
        print(f"处理刮刀数据行时出错: {e}")

# 生成MySQL插入语句
print("\n生成MySQL插入语句:")
sql_statements = []

for item in all_data:
    product_id = item['product_id']
    product_name = item['product_name']
    fix_type = item['fix_type']
    fix_sn = item['fix_sn']
    fix_status = item['fix_status']
    fix_surface = item['fix_surface']

    # 处理NULL值
    product_name_sql = f"'{product_name}'" if product_name else "NULL"
    fix_surface_sql = f"'{fix_surface}'" if fix_surface else "NULL"

    # 移除id字段
    sql = f"INSERT INTO smt_fix_config (product_id, product_name, fix_type, fix_sn, fix_status, fix_surface) " \
          f"VALUES ('{product_id}', {product_name_sql}, '{fix_type}', '{fix_sn}', '{fix_status}', {fix_surface_sql});"

    sql_statements.append(sql)
    print(sql)

# 将SQL语句写入文件
with open('insert_fixtures.sql', 'w', encoding='utf-8') as f:
    for sql in sql_statements:
        f.write(sql + '\n')

print("\nSQL语句已导出到 insert_fixtures.sql 文件")
